#!/usr/bin/env node

const mysql = require('mysql2/promise'),
      log   = console.log,
      Koa   = require('koa'),
      app   = new Koa(),
      Router = require('koa-router'),
      router = new Router(),
      fs    = require('fs'),
      path  = require('path'),
      cors  = require('koa2-cors'),
      http  = require('http'),
      https = require('https');

let con = mysql.createPool({
    host:'154.8.156.240',
    port:'2020',
    user:'root',
    password:'Ljj0222.',
    database:'tuijian'
});
//通过userid查询main_table内容
async function getid_home(){
    const sql = `select share_id,shop_id,title,date,addr,user_name,user_img,food,tags from main_table left join user_table on user_table.id = main_table.user_id `;
    let [rows] = await con.execute(sql);
    return rows;
}
//通过shop_id查询商家数据
async function getid_shop(pagesize,pagenum){
    const sql =`select id,shop_name,shop_jwd,shop_img,shop_label,shop_starcount,shop_count,shop_distribution1,shop_distribution2,shop_reduction1,shop_reduction2,shop_reduction3 from shop_table limit ${pagesize},${pagenum}`;
    let [rows] = await con.execute(sql);
    return rows;
}
//查询总数
async function getnum(){
  const sql =`SELECT COUNT(*) as count from shop_table`;
  let [rows] = await con.execute(sql);
  return rows;
}
//通过shop_id查询商家详情页
async function getid_shop_details(id){
	  const sql =`select id,shop_name,shop_tel,shop_addr,shop_jwd,shop_img,shop_mtid,shop_announcementinfo from shop_table where id = '${id}';`;
    //log(sql);
	  let [rows] = await con.execute(sql);
	  return rows;
}
//通过shop_id和user_id查询状态
async function getuser_shop_state(user_id,shop_id){
    const sql = `select state from concern_shop_table where user_id = '${user_id}' and shop_id ='${shop_id}'`;
    let [rows] = await con.execute(sql);
    return rows;
}

//添加shop_name模糊搜索
async function getname_shop(name){
    const sql =`select id,shop_name,shop_jwd,shop_img,shop_label,shop_starcount,shop_count,shop_distribution1,shop_distribution2,shop_reduction1,shop_reduction2,shop_reduction3 from shop_table where shop_name like '%${name}%' or shop_addr like '%${name}%'`;
    let [rows] = await con.execute(sql);
    return rows;
}
//商家入驻添加信息
async function insert_shop(id,name,tel,addr,jwd,img,mtid,r1,r2,r3,announcementinfo){
    const sql = `insert into shop_table(id,shop_name,shop_tel,shop_addr,shop_jwd,shop_img,shop_mtid,shop_reduction1,shop_reduction2,shop_reduction3,shop_announcementinfo) values ('${id}','${name}','${tel}','${addr}','${jwd}','${img}','${mtid}','${r1}','${r2}','${r3}','${announcementinfo}')`;
    let [rows] = await con.execute(sql);
    return rows;
}
//商家倒闭退出
async function delid_shop(id){
    const sql = `delete from shop_table where id = '${id}'`;
    let [rows] = await con.execute(sql);
    return rows;
}
//关注商家信息表
async function delconcern_shop(id){
    const sql = `delete from concern_shop_table where shop_id = '${id}'`;
    let [rows] = await con.execute(sql);
    return rows;
}

(async function(){
    //log(await getAreas());
})();

module.exports.getid_home = getid_home;
module.exports.getid_shop = getid_shop;
module.exports.getid_shop_details = getid_shop_details;
module.exports.getname_shop = getname_shop;
module.exports.insert_shop = insert_shop;
module.exports.delid_shop = delid_shop;
module.exports.getuser_shop_state = getuser_shop_state;
module.exports.delconcern_shop = delconcern_shop;
module.exports.getnum = getnum;
